package dhee.wtqshopproject.dao;

import dhee.wtqshopproject.entity.Product;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

public class ProductSqlProvider {

    // 根据分类ID分页查询前40个展示商品
    public String selectByCategoryWithLimit(@Param("categoryId") Integer categoryId,@Param("offset") int offset) {
        return new SQL() {{
            SELECT("*");
            FROM("product");
            WHERE("category_id = #{categoryId}");
            WHERE("is_show = 1");
            ORDER_BY("create_time DESC");
        }}.toString() + " LIMIT #{offset}, 6";
    }


    // 查询每个分类前6个商品
    public String selectByCategoriesWithLimit() {
                // 内层查询：生成带行号的子查询
                String innerSql = new SQL() {{
                    SELECT("*");
                    SELECT("ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY create_time DESC) AS row_num");
                    FROM("product");
                    WHERE("is_show = 1");
                }}.toString();
                // 外层查询：过滤行号 <=6
                return "SELECT * FROM (" + innerSql + ") sub WHERE row_num <= 6";
            }
        }


